Returns the character specified by the code number n.

=char(65) → "A"

Removes all non-printable characters from the specified text.

=clean(char(7) & "abc") → "abc"

Returns a numeric code for the first character in a text string.

=code("ABC") → 65

Merges all strings specified by the arguments, which can be text strings, numbers, or arrays. Empty cells in arrays are ignored.

=concatenate("abc", 1, "abc", 2) → "abc1abc2"

Converts a number x to a text string using the default currency format and the specified precision. If the decimals argument is greater than 0, then x is rounded to the specified number of decimal places. If it is 0, then x is rounded to the nearest integer. If it is less than 0, then x is rounded to the left of the decimal point. The default value of decimals is 2.

=dollar(10.95,) → "$10.95"

=dollar(10.9487, 3) → "$10.949"

Checks whether two text strings are exactly the same. Returns 1 if they are identical and 0 otherwise. The comparison is case-sensitive.

=exact("Abc", "abc") → 0

=exact("Abc", "Abc") → 1

Searches subject for a pattern starting at the position start and returns the position of the first occurrence found (except when using the "RegExStr" flag). If the pattern is not found or if start is out of the valid range, it returns the #VALUE! error.

The first version accepts a plain text string as the pattern and the comparison is case-sensitive.

The second version also accepts regular expressions as the pattern and can perform both case-sensitive and caseless comparisons. The flags parameter can be any combination of the following options:

• SEARCH::RegEx (or 8192) - pattern is a regular expression.
• SEARCH::RegExStr (or 131072) - pattern is a regular expression; the matching substring is returned instead of its position.
• SEARCH::CaseSensitive (or 128) - use case-sensitive string comparison (regular expressions can overwrite this).

=find("text", "sample text", 1) → 8

=find("e*\d", "abcdef abcee abcde5", 1, SEARCH::RegEx) → 18

Rounds x to the specified number of digits and formats it as a string using the general format. If the digits argument is greater than 0, then x is rounded to the specified number of decimal places. If it is 0, x is rounded to the nearest integer. If it is less than 0, x is rounded to the left of the decimal point. If no_separators is 0 (default), the number is formatted using thousand separators. If digits is omitted, it is assumed to be 2.

=fixed(10.956,,) → "10.96"

=fixed(1234.89, -2, 0) → "1,200"

Returns the first n characters from the start of a text string. If n is omitted, it is assumed to be 1. If n is greater than the number of characters in the text, the entire string is returned.

=left("abc",) → "a"

Returns the number of characters in a text string.

=len("text") → 4

Returns the number of bytes in a text string. Since text is internally stored as UTF-8, the number of bytes will be larger than the number of characters for strings containing non-ASCII characters (characters with codes above 127).

=lenb("örtlich") → 8

Converts all letters in a text string to lowercase.

=lower("TEXT") → "text"

Returns up to n2 characters from text starting at the position n1. If n1 is greater than the total number of characters, it returns an empty string. If n1 is less than 1 or if n2 is negative, it returns the #VALUE! error.

=mid("some text", 3, 2) → "me"

Converts the first character of each word in a text string to uppercase and all other characters to lowercase.

=proper("some teXT") → "Some Text"

The first version removes n2 characters from subject starting at position n1 and replaces them with the string replace.

The second version searches subject for pattern starting at position start and replaces all found occurrences with replace. The pattern parameter can be either a plain text string or a regular expression. The flags parameter can be any combination of the following:

• SEARCH::RegEx (or 8192) - pattern is a regular expression.
• SEARCH::CaseSensitive (or 128) - use case-sensitive comparison (regular expressions can overwrite this).

If SEARCH::RegEx is specified, the replace argument can contain:

• (1) Absolute references (by number) to capturing subpatterns, e.g., \1, \2...\999.
• (2) \l, \L, \u, \U literals for binary switching of upper- and lower-case conversion.
• (3) \r, \n - 'line feed' and 'new line' literals (pressing Ctrl+Enter in a cell typically inserts a \r\n sequence).

=replace("abcd", 3, 2, "*") → "ab*"

=replace("(.)a+\d{1,3}", "abc aa0102", 1, "\1", SEARCH::RegEx) → "abc 2"

=replace("(ab)", "abcdef ghijk abb123", 1, "\u\1", SEARCH::RegEx) → "ABcdef ghijk ABb123"

Repeats a text string n times.

=rept("abc", 3) → "abcabcabc"

Returns the last n characters from a text string. If n is omitted, it is assumed to be 1. If n is greater than the number of characters in the text, the entire string is returned.

=right("abc",) → "c"

Finds the first occurrence of searchText within withinText and returns its position. It starts searching at position n. If n is omitted, it is assumed to be 1 (the first character). The searchText argument can contain special characters: ? (matching any single character) or * (matching any string, including an empty string). To search for a literal ? or *, place a tilde (~) before them.

=search("text", "some text", 1) → 6

=search("?bc?e*", "abc abcd abcde ab",) → 10

Finds the n-th occurrence of searchText in withinText and substitutes searchText with replaceText. To replace all occurrences, specify 0 for the n parameter. The default value of n is 0.

=substitute("a1b1c1d", "1", "-", 0) → "a-b-c-d"

=substitute("abc abcd abcde ab", "?bc?e*a", "x", 1) → "abc abcd xb"

If the value parameter is a text string, the t function returns that string. Otherwise, it returns an empty string.

Formats a given number or text using the specified format code.

If the function is used to format a date, the value argument must be either a date/time serial number or a generic date/time string. For more information about format codes, please see the "Formatting: Style" help topic.

=text(-2345.4, "\$#,##0.00") → "-$2,345.40"

=text(-2345.4, "#,##0.00;\(#,##0.00\)") → "(2,345.40)"

=text("bc", "\a@\d") → "abcd"

=text(date(2009,10,21), "mmmm\ d\,\ yyyy") → "October 21, 2009"

=text("2009-10-21", "mmmm\ d\,\ yyyy") → "October 21, 2009"

Removes all whitespace from text except for single spaces between words.

=trim(" abc def ghi") → "abc def ghi"

Removes leading and trailing spaces from a given text string. Unlike the trim() function, trimE does not compress inner spaces.

=trimE(" abc def ") → "abc def"

Removes only the leading spaces from a given text string.

=trimL(" abc def ") → "abc def "

Removes only the trailing spaces from a given text string.

=trimR(" abc def ") → " abc def"

Converts all letters in a text string to uppercase.

=upper("text") → "TEXT"

Converts a text string that represents a number to a numeric value.

=value("123.45") → 123.45

=value("10 4/5") → 10.8

=value("$1,000") → 1000